package com.rybbaby.tss.front.tssStudent.service.imp;

import com.rybbaby.tss.core.web.service.imp.BaseServiceImp;
import com.rybbaby.tss.front.tssPertainRelation.dao.ITssPertainRelationDao;
import com.rybbaby.tss.front.tssPertainRelation.entity.TssPertainRelation;
import com.rybbaby.tss.front.tssStudent.dao.ITssStudentDao;
import com.rybbaby.tss.front.tssStudent.dao.ITssStudentImportDao;
import com.rybbaby.tss.front.tssStudent.entity.StudentImport;
import com.rybbaby.tss.front.tssStudent.entity.TssStudent;
import com.rybbaby.tss.front.tssStudent.entity.TssStudentExport;
import com.rybbaby.tss.front.tssStudent.service.StudentImportService;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 客户信息解析上传文件 并导入
 * 杨然
 * 2017年11月21日14:33:21
 * */
@Service
public class StudentImportServiceImp extends BaseServiceImp<StudentImport> implements StudentImportService {

    @Autowired
    private ITssStudentDao tssStudentDao;
    @Autowired
    private ITssPertainRelationDao pertainRelationDao;
    @Autowired
    private ITssStudentImportDao studentImportDao;

    /**
     * 解析验证导入信息
     * */
    @Override
    public List<Map<String, Object>> stuImport(HttpServletRequest request,String adminUserId) throws Exception {
        //构造StringBuffer存储错误信息
        //StringBuffer reg = new StringBuffer();

        //存储错误信息
        List<Map<String, Object>> errorList = new ArrayList<>();
        int successSize = 0;
        int errorSize = 0;
        int i = 0;

        //初始化学生导入对象
        StudentImport student = null;
        List<StudentImport> list = new ArrayList();
        DecimalFormat format = new DecimalFormat("#");

        MultipartHttpServletRequest mReq = (MultipartHttpServletRequest) request;
        /*filePath:<input type="file" name="filePath"/>*/
        MultipartFile file = mReq.getFile("filePath");
        if (file.isEmpty()){
            HashMap<String, Object> errorMap = new HashMap<>();
            errorMap.put("error","请选择导入文件");
            errorList.add(errorMap);
            return errorList;
        }
        String filename = file.getOriginalFilename();

        String s = filename.substring(filename.lastIndexOf("."));
        System.out.println(s);


        //HSSFWorkBook  97~03	xls
        //XSSFWorkBook 	07~		xlsx
        Workbook wb = null;
        if(s.equals(".xls")){
            wb = new HSSFWorkbook(file.getInputStream());
            HashMap<String, Object> errorMap = new HashMap<>();
            errorMap.put("suffix",s);
            errorList.add(errorMap);
        }else if(s.equals(".xlsx")){
            wb = new XSSFWorkbook(file.getInputStream());
            HashMap<String, Object> errorMap = new HashMap<>();
            errorMap.put("suffix",s);
            errorList.add(errorMap);
        }else{
            HashMap<String, Object> errorMap = new HashMap<>();
            errorMap.put("error","加载Excel错误，请重新导入");
            errorList.add(errorMap);
            return errorList;
            //return reg.append("加载Excel错误，请重新导入");
        }

        Sheet sheet = wb.getSheetAt(0);


        boolean correct = true;
        long lastrowN=sheet.getLastRowNum();

        /*解析每行每列*/
        for (Row row : sheet) {
            int rowNum = row.getRowNum();
            Map<String, Object> errorMap = new HashMap<>();


            if(rowNum >= 1){
                i++;
                student = new StudentImport();
                student.setId(i);

                //获取幼儿数据
                Cell cell1 = row.getCell(0);
                if (cell1 != null && cell1.getCellType() != HSSFCell.CELL_TYPE_BLANK) {
                    String name = cell1.getStringCellValue();
                    String nameReg = "^([\\u4e00-\\u9fa5]+|([a-zA-Z]+\\s?)+)$";

                    if (name.matches(nameReg)){
                        errorMap.put("name",cell1.getStringCellValue());
                        student.setName(name);
                    } else{
                        correct = false;
                        errorMap.put("name",cell1.getStringCellValue()+"|请使用中文");
                    }
                }else {
                    correct = false;
                    errorMap.put("name","|幼儿姓名必填");
                }

                Cell cell2 = row.getCell(1);
                if (cell2 != null && cell2.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    errorMap.put("nickName",cell2.getStringCellValue());
                    student.setNickName(cell2.getStringCellValue());
                }else{
                    correct = false;
                    errorMap.put("nickName","");
                }

                Cell cell3 = row.getCell(2);
                if (cell3 != null && cell3.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    if (cell3.getStringCellValue().equals("男")) {
                        student.setSex(1);
                        errorMap.put("sex", cell3.getStringCellValue());
                    } else if (cell3.getStringCellValue().equals("女")) {
                        student.setSex(2);
                        errorMap.put("sex", cell3.getStringCellValue());
                    } else {
                        correct = false;
                        errorMap.put("sex", cell3.getStringCellValue() + "|请输入男或女");
                    }
                }else {
                    correct = false;
                    errorMap.put("sex","");
                }

                Cell cell4 = row.getCell(3);
                if (cell4 != null && cell4.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    if (HSSFDateUtil.isCellDateFormatted(cell4)){
                        Date date = cell4.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        String format1 = sdf.format(date);
                        student.setBirthday(date);
                        errorMap.put("birthday",cell4.getStringCellValue());
                    } else{
                        cell4.setCellType(1);
                        String bir = cell4.getStringCellValue();
                        StringBuffer sb = new StringBuffer(bir);
                        sb.insert(6,"-");
                        sb.insert(4,"-");
                        bir = sb.toString();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        Date date = sdf.parse(bir);
                        student.setBirthday(date);
                        errorMap.put("birthday",cell4.getStringCellValue());
                    }
                }else {
                    correct = false;
                    errorMap.put("birthday","");
                }

                //获取妈妈的数据
                Cell cell5 = row.getCell(4);
                //errorMap.put("motherPhone",cell5.getStringCellValue());
                if (cell5 != null && cell5.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                    String nameReg = "^\\d+$";
                    //验证手机格式
                    if (cell5.getStringCellValue().matches(nameReg)){
                        student.setMotherPhone(cell5.getStringCellValue());
                        errorMap.put("motherPhone",cell5.getStringCellValue());
                        //验证手机号是否重复
                        /*List<TssPertainRelation> tssPertainRelationList = pertainRelationDao.findByPhone(format.format(cell5.getNumericCellValue()),adminUserId);
                        if (tssPertainRelationList.size()<1){
                            //处理excel中数值类型数据并放到对象中
                            student.setMotherPhone(format.format(cell5.getNumericCellValue()));
                            errorMap.put("motherPhone",cell5.getNumericCellValue());
                        }else {
                            correct = false;
                            errorMap.put("motherPhone",cell5.getNumericCellValue()+"|手机号已存在");
                        }*/
                    } else{
                        correct = false;
                        errorMap.put("motherPhone",cell5.getStringCellValue()+"|手机号格式不正确");
                    }
                } else{
                    student.setMotherPhone(null);
                    errorMap.put("motherPhone","");
                }

                Cell cell6 = row.getCell(5);
                if (cell6 != null && cell6.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    String nameReg = "^([\\u4e00-\\u9fa5]+|([a-zA-Z]+\\s?)+)$";
                    if (cell6.getStringCellValue().matches(nameReg)){
                        student.setMotherName(cell6.getStringCellValue());
                        errorMap.put("motherName",cell6.getStringCellValue());
                    } else{
                        correct = false;
                        errorMap.put("motherName",cell6.getStringCellValue()+"|请使用中文");
                    }
                } else{
                    student.setMotherName(null);
                    errorMap.put("motherName","");
                }

                Cell cell7 = row.getCell(6);
                if (cell7 != null && cell7.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    if(cell7.getStringCellValue().equals("决策人")){
                        student.setPositionType(1);
                        errorMap.put("positionType",cell7.getStringCellValue());
                    }else if(cell7.getStringCellValue().equals("监护人")){
                        student.setPositionType(2);
                        errorMap.put("positionType",cell7.getStringCellValue());
                    }else {
                        correct = false;
                        errorMap.put("positionType",cell7.getStringCellValue()+"|请输入决策人或监护人");
                    }
                    //student.setPositionType(cell7.getStringCellValue().equals("决策人") ? 1 : (cell7.getStringCellValue().equals("监护人") ? 2 : null));
                }else{
                    student.setPositionType(null);
                    errorMap.put("positionType","");
                }

                //获取爸爸的数据
                Cell cell8 = row.getCell(7);
                if (cell8 != null && cell8.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
                    String fatherPhoneReg = "^\\d+$";
                    if (cell8.getStringCellValue().matches(fatherPhoneReg)){
                        student.setFatherPhone(cell8.getStringCellValue());
                        errorMap.put("fatherPhone",cell8.getStringCellValue());
                        /*List<TssPertainRelation> tssPertainRelationList = pertainRelationDao.findByPhone(format.format(cell8.getNumericCellValue()),adminUserId);
                        if (tssPertainRelationList.size() <1){
                            student.setFatherPhone(format.format(cell8.getNumericCellValue()));
                            errorMap.put("fatherPhone",cell8.getNumericCellValue());
                        }else {
                            correct = false;
                            errorMap.put("fatherPhone",cell8.getNumericCellValue()+"|手机号已存在");
                        }*/
                    }else{
                        correct = false;
                        errorMap.put("fatherPhone",cell8.getStringCellValue()+"|手机号格式不正确");
                    }
                } else {
                    student.setFatherPhone(null);
                    errorMap.put("fatherPhone","");
                }

                Cell cell9 = row.getCell(8);
                if (cell9 != null && cell9.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    String nameReg = "^([\\u4e00-\\u9fa5]+|([a-zA-Z]+\\s?)+)$";
                    if (cell9.getStringCellValue().matches(nameReg)){
                        student.setFatherName(cell9.getStringCellValue());
                        errorMap.put("fatherName",cell9.getStringCellValue());
                    } else{
                        correct = false;
                        errorMap.put("fatherName",cell9.getStringCellValue()+"|请使用中文");
                    }
                } else{
                    student.setFatherName(null);
                    errorMap.put("fatherName","");
                }

                Cell cell10 = row.getCell(9);

                if (cell10 != null && cell10.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    //student.setPositionType2(cell10.getStringCellValue().equals("决策人") ? 1 : (cell10.getStringCellValue().equals("监护人") ? 2 : null));
                    if(cell10.getStringCellValue().equals("决策人")){
                        student.setPositionType2(1);
                        errorMap.put("positionType2",cell10.getStringCellValue());
                    }else if(cell10.getStringCellValue().equals("监护人")){
                        student.setPositionType2(2);
                        errorMap.put("positionType2",cell10.getStringCellValue());
                    }else {
                        correct = false;
                        errorMap.put("positionType2",cell10.getStringCellValue()+"|请输入决策人或监护人");
                    }
                } else{
                    student.setPositionType2(null);
                    errorMap.put("positionType2","");
                }

                /**
                 * 验证父母信息至少有一条是完整的
                 * */
                if (
                        (student.getMotherPhone() != null && student.getMotherName() != null && student.getPositionType() != null) ||
                        (student.getFatherPhone() != null && student.getFatherName() != null && student.getPositionType2() != null)
                    ){

                        //母亲父亲数据都完整
                        if (
                                (student.getMotherPhone() != null && student.getMotherName() != null && student.getPositionType() != null) &&
                                (student.getFatherPhone() != null && student.getFatherName() != null && student.getPositionType2() != null)
                                ){
                            if(student.getPositionType() == 1){
                                List<TssStudent> tssPertainRelationList = tssStudentDao.findByPhone(student.getMotherPhone(),adminUserId);
                                if(tssPertainRelationList.size()<1){
                                    student.setAffiliationRelation(1);
                                }else {
                                    correct = false;
                                    errorMap.put("motherPhone",cell5.getNumericCellValue()+"|手机号已存在");
                                }
                            }
                            if(student.getPositionType2() == 2){
                                List<TssStudent> tssPertainRelationList = tssStudentDao.findByPhone(student.getFatherPhone(),adminUserId);
                                if(tssPertainRelationList.size()<1){
                                    student.setAffiliationRelation(2);
                                }else {
                                    correct = false;
                                    errorMap.put("fatherPhone",cell5.getStringCellValue()+"|手机号已存在");
                                }
                            }else {
                                List<TssStudent> tssPertainRelationList = tssStudentDao.findByPhone(student.getMotherPhone(),adminUserId);
                                if(tssPertainRelationList.size()<1){
                                    student.setAffiliationRelation(1);
                                }else {
                                    correct = false;
                                    errorMap.put("motherPhone",cell5.getStringCellValue()+"|手机号已存在");
                                }
                            }
                        }

                        //母亲数据完整
                        else if (student.getMotherPhone() != "" && student.getMotherName() != null && student.getPositionType() != null){
                            student.setAffiliationRelation(3);
                        }

                        //父亲数据完整
                        else if (student.getFatherPhone() != "" && student.getFatherName() != null && student.getPositionType2() != null){
                            student.setAffiliationRelation(4);
                        }
                } else{
                    correct = false;
                    errorMap.put("motherPhone",cell5.getStringCellValue()+"|父母信息至少完整得填写一条(电话、姓名、是决策人或监护人)");
                }

                if(correct){
                    successSize = successSize+1;
                    //list.add(student);
                    TssStudent tssStudent = new TssStudent();
                    TssPertainRelation motherPertainRelation = new TssPertainRelation();
                    TssPertainRelation fatherPertainRelation = new TssPertainRelation();

                    //保存学生信息
                    tssStudent.setName(student.getName());
                    if (student.getNickName() != null)
                        tssStudent.setNickName(student.getNickName());
                    if (student.getSex() != null)
                        tssStudent.setSex(student.getSex());
                    if (student.getBirthday() != null)
                        tssStudent.setBirthdayDate(student.getBirthday());

                    //填充学生电话
                    if (student.getAffiliationRelation() == 1 || student.getAffiliationRelation() == 3){
                            tssStudent.setContactPhone(student.getMotherPhone());
                    }
                    if (student.getAffiliationRelation() == 2 || student.getAffiliationRelation() == 4){
                            tssStudent.setContactPhone(student.getFatherPhone());
                    }
                    tssStudent.setAdminUserId(adminUserId);
                    tssStudent.setCreateTime(new java.sql.Timestamp(System.currentTimeMillis()));
                    tssStudent.setUpdateTime(new java.sql.Timestamp(System.currentTimeMillis()));
                    String stuId = tssStudentDao.saveInport(tssStudent);

                    if (student.getAffiliationRelation() == 1 || student.getAffiliationRelation() == 2){
                        motherPertainRelation.setStudentId(stuId);
                        motherPertainRelation.setPatriarchName(student.getMotherName());
                        motherPertainRelation.setContactPhone(student.getMotherPhone());
                        motherPertainRelation.setPositionType(student.getPositionType());
                        motherPertainRelation.setCreateTime(new java.sql.Timestamp(System.currentTimeMillis()));
                        motherPertainRelation.setUpdateTime(new java.sql.Timestamp(System.currentTimeMillis()));

                        pertainRelationDao.save(motherPertainRelation);

                        fatherPertainRelation.setStudentId(stuId);
                        fatherPertainRelation.setPatriarchName(student.getFatherName());
                        fatherPertainRelation.setContactPhone(student.getFatherPhone());
                        fatherPertainRelation.setPositionType(student.getPositionType2());
                        fatherPertainRelation.setCreateTime(new java.sql.Timestamp(System.currentTimeMillis()));
                        fatherPertainRelation.setUpdateTime(new java.sql.Timestamp(System.currentTimeMillis()));

                        pertainRelationDao.save(fatherPertainRelation);

                    }
                    if (student.getAffiliationRelation() == 3){
                        motherPertainRelation.setStudentId(stuId);
                        motherPertainRelation.setPatriarchName(student.getMotherName());
                        motherPertainRelation.setContactPhone(student.getMotherPhone());
                        motherPertainRelation.setPositionType(student.getPositionType());
                        motherPertainRelation.setCreateTime(new java.sql.Timestamp(System.currentTimeMillis()));
                        motherPertainRelation.setUpdateTime(new java.sql.Timestamp(System.currentTimeMillis()));

                        pertainRelationDao.save(motherPertainRelation);
                    }
                    if (student.getAffiliationRelation() == 4){
                        fatherPertainRelation.setStudentId(stuId);
                        fatherPertainRelation.setPatriarchName(student.getFatherName());
                        fatherPertainRelation.setContactPhone(student.getFatherPhone());
                        fatherPertainRelation.setPositionType(student.getPositionType2());
                        fatherPertainRelation.setCreateTime(new java.sql.Timestamp(System.currentTimeMillis()));
                        fatherPertainRelation.setUpdateTime(new java.sql.Timestamp(System.currentTimeMillis()));

                        pertainRelationDao.save(fatherPertainRelation);
                    }
                }else {
                    errorSize = errorSize+1;
                    errorList.add(errorMap);
                }

            }

        }

        HashMap<String, Object> objectObjectHashMap = new HashMap<>();
        objectObjectHashMap.put("errorSize",errorSize);
        objectObjectHashMap.put("successSize",successSize);
        errorList.add(objectObjectHashMap);
        return errorList;
    }
    @Override
    public void exportListMap(String suffix,String[] headers, List<Map<String,Object>> entityList, HttpServletRequest request, HttpServletResponse response) throws IOException {

            // 声明一个工作薄
            //Workbook workbook = null;
            if(suffix.equals(".xls")){
                HSSFWorkbook workbook = new HSSFWorkbook();
                // 生成一个表格
                HSSFSheet sheet = workbook.createSheet("错误数据");
                // 设置表格默认列宽度为15个字节
                sheet.setDefaultColumnWidth((short) 15);
                // 生成一个样式
                HSSFCellStyle style = workbook.createCellStyle();
                // 设置这些样式
                style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                style.setBorderRight(HSSFCellStyle.BORDER_THIN);
                style.setBorderTop(HSSFCellStyle.BORDER_THIN);
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                // 生成一个字体
                HSSFFont font = workbook.createFont();
                font.setFontHeightInPoints((short) 10);
                //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                // 把字体应用到当前的样式
                style.setFont(font);
                // 生成并设置另一个样式
                HSSFCellStyle style2 = workbook.createCellStyle();
                style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
                style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
                style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                // 生成另一个字体
                HSSFFont font2 = workbook.createFont();
                font.setColor(HSSFColor.RED.index);
                font.setFontHeightInPoints((short) 10);
                //font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
                // 把字体应用到当前的样式
                style2.setFont(font2);



                // 产生表格标题行
                HSSFRow row = sheet.createRow(0);
                for (short i = 0; i < headers.length; i++)
                {
                    HSSFCell cell = row.createCell(i);
                    if(i == 0 || i == 4 || i == 5 || i == 6){
                        cell.setCellStyle(style2);
                    }else {
                        cell.setCellStyle(style);
                    }

                    HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                    cell.setCellValue(text);
                }

                // 遍历集合数据，产生数据行
                //entityList
                for (int i = 0; i <entityList.size() ; i++) {
                    Map<String, Object> map = entityList.get(i);
                    row = sheet.createRow(i+1);
                    Integer size = map.keySet().size();

                    HSSFCell cell0 = row.createCell(0);
                    if(map.get("name").toString().indexOf("|") != -1){
                        cell0.setCellStyle(style2);
                    }
                    cell0.setCellValue(map.get("name").toString());



                    HSSFCell cell1 = row.createCell(1);
                    if(map.get("nickName").toString().indexOf("|") != -1){
                        cell1.setCellStyle(style2);
                    }
                    cell1.setCellValue(map.get("nickName").toString());

                    HSSFCell cell2 = row.createCell(2);
                    if(map.get("sex").toString().indexOf("|") != -1){
                        cell2.setCellStyle(style2);
                    }
                    cell2.setCellValue(map.get("sex").toString());

                    HSSFCell cell3 = row.createCell(3);
                    if(map.get("birthday").toString().indexOf("|") != -1){
                        cell3.setCellStyle(style2);
                    }
                    cell3.setCellValue(map.get("birthday").toString());

                    HSSFCell cell4 = row.createCell(4);
                    if(map.get("motherPhone").toString().indexOf("|") != -1){
                        cell4.setCellStyle(style2);
                    }
                    cell4.setCellValue(map.get("motherPhone").toString());

                    HSSFCell cell5 = row.createCell(5);
                    if(map.get("motherName").toString().indexOf("|") != -1){
                        cell5.setCellStyle(style2);
                    }
                    cell5.setCellValue(map.get("motherName").toString());

                    HSSFCell cell6 = row.createCell(6);
                    if(map.get("positionType").toString().indexOf("|") != -1){
                        cell6.setCellStyle(style2);
                    }
                    cell6.setCellValue(map.get("positionType").toString());

                    HSSFCell cell7 = row.createCell(7);
                    if(map.get("fatherPhone").toString().indexOf("|") != -1){
                        cell7.setCellStyle(style2);
                    }
                    cell7.setCellValue(map.get("fatherPhone").toString());

                    HSSFCell cell8 = row.createCell(8);
                    if(map.get("fatherName").toString().indexOf("|") != -1){
                        cell8.setCellStyle(style2);
                    }
                    cell8.setCellValue(map.get("fatherName").toString());

                    HSSFCell cell9 = row.createCell(9);
                    if(map.get("positionType2").toString().indexOf("|") != -1){
                        cell9.setCellStyle(style2);
                    }
                    cell9.setCellValue(map.get("positionType2").toString());
                }
                String templateName = "错误数据请修改后重新导出"+suffix;
                response.setContentType("application/vnd.ms-excel");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(templateName, StandardCharsets.UTF_8.toString()));
                response.setCharacterEncoding(StandardCharsets.UTF_8.toString());
                ServletOutputStream outputStream = response.getOutputStream();
                workbook.write(outputStream);
                IOUtils.closeQuietly(outputStream);
            }else if(suffix.equals(".xlsx")){
                XSSFWorkbook workbook = new XSSFWorkbook();
                XSSFSheet sheet = workbook.createSheet("错误数据");
                // 设置表格默认列宽度为15个字节
                sheet.setDefaultColumnWidth((short) 15);
                // 生成一个样式
                XSSFCellStyle style = workbook.createCellStyle();
                // 设置这些样式
                //style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
                style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
                style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
                style.setBorderRight(XSSFCellStyle.BORDER_THIN);
                style.setBorderTop(XSSFCellStyle.BORDER_THIN);
                style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
                // 生成一个字体
                XSSFFont font = workbook.createFont();
                font.setFontHeightInPoints((short) 10);
                //font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
                // 把字体应用到当前的样式
                style.setFont(font);
                // 生成并设置另一个样式
                XSSFCellStyle style2 = workbook.createCellStyle();
                //style2.setFillBackgroundColor(HSSFColor.WHITE.index);
                //style2.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
                style2.setBorderBottom(XSSFCellStyle.BORDER_THIN);
                style2.setBorderLeft(XSSFCellStyle.BORDER_THIN);
                style2.setBorderRight(XSSFCellStyle.BORDER_THIN);
                style2.setBorderTop(XSSFCellStyle.BORDER_THIN);
                style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
                style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
                // 生成另一个字体
                XSSFFont font2 = workbook.createFont();
                font2.setColor(HSSFColor.RED.index);
                font2.setFontHeightInPoints((short) 10);
                // 把字体应用到当前的样式
                style2.setFont(font2);

                XSSFCellStyle style3 = workbook.createCellStyle();
                style3.setBorderBottom(XSSFCellStyle.BORDER_THIN);
                style3.setBorderLeft(XSSFCellStyle.BORDER_THIN);
                style3.setBorderRight(XSSFCellStyle.BORDER_THIN);
                style3.setBorderTop(XSSFCellStyle.BORDER_THIN);
                style3.setAlignment(XSSFCellStyle.ALIGN_CENTER);
                style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
                // 生成另一个字体
                XSSFFont font3 = workbook.createFont();
                font3.setColor(HSSFColor.RED.index);
                font3.setFontHeightInPoints((short) 10);
                // 把字体应用到当前的样式
                style3.setFont(font3);

                // 产生表格标题行
                XSSFRow row = sheet.createRow(0);
                for (short i = 0; i < headers.length; i++)
                {
                    XSSFCell cell = row.createCell(i);
                    if(i == 0 || i == 4 || i == 5 || i == 6){
                        XSSFFont font4 = workbook.createFont();
                        font4.setBoldweight(Font.BOLDWEIGHT_BOLD);
                        font4.setColor(HSSFColor.RED.index);
                        font4.setFontHeightInPoints((short) 10);
                        // 把字体应用到当前的样式
                        style3.setFont(font4);
                        cell.setCellStyle(style3);
                    }else {
                        XSSFFont font5 = workbook.createFont();
                        font5.setBoldweight(Font.BOLDWEIGHT_BOLD);
                        font5.setColor(HSSFColor.RED.index);
                        font5.setFontHeightInPoints((short) 10);
                        // 把字体应用到当前的样式
                        style3.setFont(font5);
                        cell.setCellStyle(style3);
                    }
                    XSSFRichTextString text = new XSSFRichTextString(headers[i]);
                    cell.setCellValue(text);
                }

                // 遍历集合数据，产生数据行
                //entityList
                for (int i = 0; i <entityList.size() ; i++) {
                    Map<String, Object> map = entityList.get(i);
                    row = sheet.createRow(i+1);
                    Integer size = map.keySet().size();

                    XSSFCell cell0 = row.createCell(0);
                    if(map.get("name").toString().indexOf("|") != -1){
                        cell0.setCellStyle(style2);
                    }
                    cell0.setCellValue(map.get("name").toString());



                    XSSFCell cell1 = row.createCell(1);
                    if(map.get("nickName").toString().indexOf("|") != -1){
                        cell1.setCellStyle(style2);
                    }
                    cell1.setCellValue(map.get("nickName").toString());

                    XSSFCell cell2 = row.createCell(2);
                    if(map.get("sex").toString().indexOf("|") != -1){
                        cell2.setCellStyle(style2);
                    }
                    cell2.setCellValue(map.get("sex").toString());

                    XSSFCell cell3 = row.createCell(3);
                    if(map.get("birthday").toString().indexOf("|") != -1){
                        cell3.setCellStyle(style2);
                    }
                    cell3.setCellValue(map.get("birthday").toString());

                    XSSFCell cell4 = row.createCell(4);
                    if(map.get("motherPhone").toString().indexOf("|") != -1){
                        cell4.setCellStyle(style2);
                    }
                    cell4.setCellValue(map.get("motherPhone").toString());

                    XSSFCell cell5 = row.createCell(5);
                    if(map.get("motherName").toString().indexOf("|") != -1){
                        cell5.setCellStyle(style2);
                    }
                    cell5.setCellValue(map.get("motherName").toString());

                    XSSFCell cell6 = row.createCell(6);
                    if(map.get("positionType").toString().indexOf("|") != -1){
                        cell6.setCellStyle(style2);
                    }
                    cell6.setCellValue(map.get("positionType").toString());

                    XSSFCell cell7 = row.createCell(7);
                    if(map.get("fatherPhone").toString().indexOf("|") != -1){
                        cell7.setCellStyle(style2);
                    }
                    cell7.setCellValue(map.get("fatherPhone").toString());

                    XSSFCell cell8 = row.createCell(8);
                    if(map.get("fatherName").toString().indexOf("|") != -1){
                        cell8.setCellStyle(style2);
                    }
                    cell8.setCellValue(map.get("fatherName").toString());

                    XSSFCell cell9 = row.createCell(9);
                    if(map.get("positionType2").toString().indexOf("|") != -1){
                        cell9.setCellStyle(style2);
                    }
                    cell9.setCellValue(map.get("positionType2").toString());
                }
                String templateName = "错误数据请修改后重新导出"+suffix;
                response.setContentType("application/vnd.ms-excel");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(templateName, StandardCharsets.UTF_8.toString()));
                response.setCharacterEncoding(StandardCharsets.UTF_8.toString());
                ServletOutputStream outputStream = response.getOutputStream();
                workbook.write(outputStream);
                IOUtils.closeQuietly(outputStream);
            }



    }

    @Override
    public List<Map<String, Object>> importPreview(HttpServletRequest request) throws IOException, ParseException {

        List<Map<String, Object>> errorList = new ArrayList<>();

        MultipartHttpServletRequest mReq = (MultipartHttpServletRequest) request;
        /*filePath:<input type="file" name="filePath"/>*/
        MultipartFile file = mReq.getFile("filePath");
        if (file.isEmpty()){
            HashMap<String, Object> errorMap = new HashMap<>();
            errorMap.put("error","请选择导入文件");
            errorList.add(errorMap);
            return errorList;
        }
        String filename = file.getOriginalFilename();

        String s = filename.substring(filename.lastIndexOf("."));
        System.out.println(s);


        //HSSFWorkBook  97~03	xls
        //XSSFWorkBook 	07~		xlsx
        Workbook wb = null;
        if(s.equals(".xls")){
            wb = new HSSFWorkbook(file.getInputStream());
        }else if(s.equals(".xlsx")){
            wb = new XSSFWorkbook(file.getInputStream());
        }else{
            HashMap<String, Object> errorMap = new HashMap<>();
            errorMap.put("error","加载Excel错误，请重新导入");
            errorList.add(errorMap);
            return errorList;
        }

        Sheet sheet = wb.getSheetAt(0);


        boolean correct = true;

        /*解析每行每列*/
        for (Row row : sheet) {
            int rowNum = row.getRowNum();
            Map<String, Object> errorMap = new HashMap<>();

            if(rowNum < 1){
                Cell cell1 = row.getCell(0);
                if (cell1 != null && cell1.getCellType() != HSSFCell.CELL_TYPE_BLANK) {
                    String name = cell1.getStringCellValue();

                    if (cell1.getStringCellValue().equals("幼儿姓名")){
                        errorMap.put("name",cell1.getStringCellValue());
                    } else{
                        correct = false;
                    }
                }else {
                    correct = false;
                }

                Cell cell2 = row.getCell(1);
                if (cell2 != null && cell2.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    if (cell2.getStringCellValue().equals("幼儿昵称")){
                        errorMap.put("nickName",cell2.getStringCellValue());
                    } else{
                        correct = false;
                    }
                }else{
                    correct = false;
                }

                Cell cell3 = row.getCell(2);
                if (cell3 != null && cell3.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    if (cell3.getStringCellValue().equals("幼儿性别")){
                        errorMap.put("sex", cell3.getStringCellValue());
                    } else{
                        correct = false;
                    }
                }else {
                    correct = false;
                }

                Cell cell4 = row.getCell(3);
                if (cell4 != null && cell4.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    if (cell4.getStringCellValue().equals("出生日期")){
                        errorMap.put("birthday", cell4.getStringCellValue());
                    } else{
                        correct = false;
                    }
                }else {
                    correct = false;
                }

                //获取妈妈的数据
                Cell cell5 = row.getCell(4);
                if (cell5 != null && cell5.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    if (cell5.getStringCellValue().equals("妈妈（电话）")){
                        errorMap.put("motherPhone", cell5.getStringCellValue());
                    } else{
                        correct = false;
                    }
                } else{
                    correct = false;
                }

                Cell cell6 = row.getCell(5);
                if (cell6 != null && cell6.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    if (cell6.getStringCellValue().equals("妈妈（姓名）")){
                        errorMap.put("motherName", cell6.getStringCellValue());
                    } else{
                        correct = false;
                    }
                } else{
                    correct = false;
                }

                Cell cell7 = row.getCell(6);
                if (cell7 != null && cell7.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    if (cell7.getStringCellValue().equals("决策人/监护人")){
                        errorMap.put("positionType", cell7.getStringCellValue());
                    } else{
                        correct = false;
                    }
                }else{
                    correct = false;
                }

                //获取爸爸的数据
                Cell cell8 = row.getCell(7);
                if (cell8 != null && cell8.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    if (cell8.getStringCellValue().equals("爸爸（电话）")){
                        errorMap.put("fatherPhone", cell8.getStringCellValue());
                    } else{
                        correct = false;
                    }
                } else {
                    correct = false;
                }

                Cell cell9 = row.getCell(8);
                if (cell9 != null && cell9.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    if (cell9.getStringCellValue().equals("爸爸（姓名）")){
                        errorMap.put("fatherName", cell9.getStringCellValue());
                    } else{
                        correct = false;
                    }
                } else{
                    correct = false;
                }

                Cell cell10 = row.getCell(9);

                if (cell10 != null && cell10.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    if (cell10.getStringCellValue().equals("决策人/监护人")){
                        errorMap.put("positionType2", cell10.getStringCellValue());
                    } else{
                        correct = false;
                    }
                } else{
                    correct = false;
                }
                if(!correct){
                    errorMap.put("error","加载Excel错误，请重新导入");
                    errorList.add(errorMap);
                    return errorList;
                }
            }
            if(rowNum >= 1){

                //获取幼儿数据
                Cell cell1 = row.getCell(0);
                if (cell1 != null && cell1.getCellType() != HSSFCell.CELL_TYPE_BLANK) {
                    errorMap.put("name",cell1.getStringCellValue()+"|请使用中文");
                }

                Cell cell2 = row.getCell(1);
                if (cell2 != null && cell2.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    errorMap.put("nickName",cell2.getStringCellValue());
                }

                Cell cell3 = row.getCell(2);
                if (cell3 != null && cell3.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    errorMap.put("sex", cell3.getStringCellValue());
                }

                Cell cell4 = row.getCell(3);
                if (cell4 != null && cell4.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    if (HSSFDateUtil.isCellDateFormatted(cell4)){
                        Date date = cell4.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        String format1 = sdf.format(date);
                        errorMap.put("birthday",cell4.getStringCellValue());
                    } else{
                        cell4.setCellType(1);
                        String bir = cell4.getStringCellValue();
                        StringBuffer sb = new StringBuffer(bir);
                        sb.insert(6,"-");
                        sb.insert(4,"-");
                        bir = sb.toString();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        Date date = sdf.parse(bir);
                        errorMap.put("birthday",cell4.getStringCellValue());
                    }
                }

                //获取妈妈的数据
                Cell cell5 = row.getCell(4);
                if (cell5 != null && cell5.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                    errorMap.put("motherPhone",cell5.getStringCellValue());
                }

                Cell cell6 = row.getCell(5);
                if (cell6 != null && cell6.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    errorMap.put("motherName",cell6.getStringCellValue());
                }

                Cell cell7 = row.getCell(6);
                if (cell7 != null && cell7.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    errorMap.put("positionType",cell7.getStringCellValue());
                }

                //获取爸爸的数据
                Cell cell8 = row.getCell(7);
                if (cell8 != null && cell8.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
                    errorMap.put("fatherPhone",cell8.getStringCellValue());
                }

                Cell cell9 = row.getCell(8);
                if (cell9 != null && cell9.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    errorMap.put("fatherName",cell9.getStringCellValue());
                }

                Cell cell10 = row.getCell(9);
                if (cell10 != null && cell10.getCellType() != HSSFCell.CELL_TYPE_BLANK){
                    errorMap.put("positionType2",cell10.getStringCellValue());
                }
            }
            errorList.add(errorMap);
        }
        return errorList;
    }
}
